package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"strconv"
	"strings"
	"time"
)

type TGroupUserInfo struct {
	Id        int64 `gorm:"column:id",json:"id"`
	PlatId    int64 `gorm:"column:plat_id",json:"plat_id"` //
	GroupId   int64 `gorm:"column:group_id",json:"group_id"`
	UserCount int64 `gorm:"column:user_count",json:"user_count"`
	Data      int64 `gorm:"column:data_status",json:"data_status"`
}

type TGroupInformation struct {
	Id         int64  `gorm:"column:id",json:"id"`           // 自增
	PlatId     string `gorm:"column:plat_id",json:"plat_id"` //
	GroupName  string `gorm:"column:group_name",json:"group_name"`
	GroupId    string `gorm:"column:group_id",json:"group_id"`
	CreateTime string `gorm:"column:create_time",json:"create_time"`
}
type TGroupInformationHaha struct {
	Id         int64  `gorm:"column:id",json:"id"`           // 自增
	PlatI      string `gorm:"column:plat_id",json:"plat_id"` //
	GroupNam   string `gorm:"column:group_name",json:"group_name"`
	GroupI     string `gorm:"column:group_id",json:"group_id"`
	CreateTime string `gorm:"column:create_time",json:"create_time"`
}

type TUserFriendRemarks struct {
	CreateTime   time.Time `xorm:"default 'CURRENT_TIMESTAMP' comment('创建时间') DATETIME"`
	DelFlag      int       `xorm:"not null default 0 comment('是否删除(0 未删除，1已删除)') TINYINT(1)"`
	FriendId     int64     `xorm:"not null comment('上下级好友iD') index(Idx_plat_user_friend) BIGINT"`
	FriendRemark string    `xorm:"comment('好友备注') index(Idx_plat_user_friend) VARCHAR(255)"`
	FriendType   int       `xorm:"not null comment('好友类型(1上级好友， 2下级好友)') index(Idx_plat_user_friend) INT"`
	Id           int       `xorm:"not null pk autoincr comment('主键自增ID') INT"`
	PlatId       int64     `xorm:"not null comment('平台ID') index(Idx_plat_user_friend) BIGINT"`
	UpdateTime   time.Time `xorm:"default 'CURRENT_TIMESTAMP' comment('更新时间') DATETIME"`
	UserId       int64     `xorm:"not null comment('用户ID') index(Idx_plat_user_friend) BIGINT"`
}

func main() {
	dbRelation, err := gorm.Open("mysql", "chat:Abc123!@#.@(43.255.53.66:13306)/chat_relations?charset=utf8")
	if err != nil {
		panic("连接数据库失败")
	}
	defer dbRelation.Close()

	if err != nil {
		fmt.Println(err)
		return
	}

	dbCustomer, err := gorm.Open("mysql", "chat:Abc123!@#.@(43.255.53.66:13306)/chat_customer?charset=utf8")
	if err != nil {
		panic("连接数据库失败")
	}
	defer dbRelation.Close()
	dbCustomer.LogMode(true)
	if err != nil {
		fmt.Println(err)
		return
	}

	//  chat_customer 数据库连接
	TestChatCustomer(dbCustomer)
	// chat_relations 数据库连接
	TestChatRelation(dbRelation)

}

func TestChatRelation(db *gorm.DB) {

	//var count int64
	//err = db.Table("t_group_information").Row().Scan(&count)
	//if err != nil {
	//	fmt.Println("err:", err)
	//} else {
	//	fmt.Println(count)
	//}
	//
	//// 查询数量用这个
	//err = db.Table("t_group_information").Select("count(1) count").Count(&count).Error
	//if err != nil {
	//	fmt.Println("err:", err)
	//} else {
	//	fmt.Println(count)
	//}
	//
	//// 更新一条数据
	////err = db.Table("t_group_information").Exec("").Error
	////if err != nil{
	////	fmt.Println("err:", err)
	////}else {
	////	fmt.Println(count)
	////}
	//
	//str := db.Table("t_group_information").Select("id, group_name, group_id, create_time").Where("plat_id=?", 190051).SubQuery()
	//fmt.Println(str)
	////data := make([]*TGroupInformation, 0)
	////err = db.Raw("select * from ? as t1", str).Find(&data).Error
	////if err != nil{
	////	fmt.Println("获取数据错误", err)
	////}else {
	////	for i := range data{
	////		fmt.Println("获取数据成功：", data[i])
	////	}
	////}
	//
	//count1 := 0
	//// raw 的用法
	//sql, err := db.Raw("select count(1) as count from t_group_information").Rows()
	//if err != nil {
	//	fmt.Println("获取数据错误", err)
	//} else {
	//	for sql.Next() {
	//		err = sql.Scan(&count1)
	//		if err != nil {
	//			fmt.Println(err)
	//			return
	//		}
	//		fmt.Println("总数为：", count1)
	//		//for i := range data{
	//		//	fmt.Println("获取数据成功：", data[i])
	//		//}
	//	}
	//
	//}
	//
	//data3 := make([]*TGroupInformation, 0)
	//// raw 的用法
	//err = db.Raw("select * from t_group_information where plat_id=? and create_time>0 ", 190051).Find(&data3).Error
	//if err != nil {
	//	fmt.Println("获取数据错误", err)
	//} else {
	//	fmt.Println("data3:", data3)
	//
	//}

	//data1 := make([]*TGroupInformation, 0)
	//// raw 的用法
	//err = db.Raw("select * from t_group_information where plat_id = 190051").Scan(&data1).Error
	//if err != nil{
	//	fmt.Println("获取数据错误", err)
	//}else {
	//	fmt.Println(data1)
	//
	//}

	// sql21 := "SELECT * FROM `chat_customer`.`t_group_information` WHERE `plat_id` = '190083' "
	//data21 := make([]*TGroupInformation, 0)
	//err = db.Table("t_group_information").
	//	Where("plat_id !=?", 190051).
	//	Where("plat_id not in (190081, 190045)").
	//	//Exec("SELECT * FROM `chat_customer`.`t_group_information` WHERE `plat_id` = '190083' ").
	//	Find(&data21).Error
	//if err!= nil{
	//	fmt.Println(err)
	//}else {
	//	fmt.Println("data21的数据为：")
	//	for i := range data21{
	//		fmt.Println(data21[i])
	//	}
	//}

	//userInfoGroup := make([]*TGroupUserInfo, 0)
	//err = db.Table("t_group_user t1").Select("count(t1.id) user_count, t1.plat_id, t1.group_id, sum(t1.role_id) data_status").
	//	Group("group_id").
	//	Where("plat_id=190051").
	//	Having("user_count > 10").
	//	Having("group_id IN (?)", []int64{111200001}).
	//	Find(&userInfoGroup).Error
	//info := new(TGroupUserInfo)
	//err = db.Select("").Raw("select sum(user_count) user_count, sum(group_id) group_id, plat_id  from ?  t1 GROUP BY plat_id", sqlStr).First(info).Error
	//if err != nil{
	//	fmt.Println("分组错误：", err)
	//}else {
	//	fmt.Println("分组信息为：", userInfoGroup)
	//	for i := range userInfoGroup{
	//		fmt.Println(userInfoGroup[i])
	//	}
	//}

	//	data111 := make([]*TGroupUserInfo, 0)
	//	sqlStr := "select * from t_group_user  where group_id=?"
	//	err = db.Raw(sqlStr, 319400007).Find(&data111).Error
	//	if err != nil{
	//		fmt.Println("获取错误:", err)
	//	}else {
	//		fmt.Println("获取当前群组成员成功：", data111)
	//	}

	//userFriendInfo := new(TUserFriendRemarks)
	//userFriendInfo.FriendRemark = "小丸子2"
	//userFriendInfo.FriendType = 1
	//userFriendInfo.UserId = 3204000002
	//userFriendInfo.PlatId = 190051
	//userFriendInfo.FriendId = 111111111
	//userFriendInfo.CreateTime = time.Now()
	//userFriendInfo.UpdateTime = time.Now()
	/*	userFriendInfo1 := new(TUserFriendRemarks)
		userFriendInfo.FriendRemark = "小丸子3"
		userFriendInfo.FriendType = 1
		userFriendInfo.UserId = 3204000002
		userFriendInfo.PlatId = 190051
		userFriendInfo.FriendId = 2222222222
		userFriendInfo.CreateTime = time.Now()
		userFriendInfo.UpdateTime = time.Now()*/
	/*data := make([]*TUserFriendRemarks, 0)
	data = append(data, userFriendInfo, userFriendInfo1)
	err = db.Table("t_user_friend_remark").Select("plat_id, user_id, friend_type, friend_id, friend_remark").Update(&data).Error
	if err != nil{
		fmt.Println("新增用户备注信息失败：", err)
	}*/

	//userFriendInfo3 := new(TUserFriendRemarks)
	//fmt.Println(userFriendInfo3)
	//if userFriendInfo3 == nil{
	//	fmt.Println("userFriendInfo3 不存在")
	//}else {
	//	fmt.Println("userFriendInfo3 存在")
	//}
	//
	//var count int64
	//err = db.Table("t_user_friend_remark").Where("id=?", 111).Count(&count).Error
	//if err != nil{
	//	fmt.Println("asdfafdsafdddd", err)
	//}
	//fmt.Println("数量为：", count)
	//var has bool
	//fmt.Println(has)

}

func TestGroupInfo(db gorm.DB) {
	data := make([]*TGroupUserInfo, 0)
	sql := db.Table("t_group_user").Select("max(id) as id").Group("group_id").SubQuery()
	aadd := []int64{8514, 9331, 9298}
	tmp := " "
	for _, v := range aadd {
		tmp += strconv.Itoa(int(v)) + ", "
	}

	sqlWhere := fmt.Sprintf("where t1.id in (%s) ", strings.Trim(tmp, ", "))
	err := db.Raw("select * from ? as t1 "+sqlWhere, sql).Limit(5).Offset(0).Find(&data).Error
	if err != nil {
		fmt.Println(err)
	} else {
		for i := range data {
			fmt.Println(data[i])
		}
	}

	dataStr := fmt.Sprintf(" (%v)", data)
	fmt.Println(dataStr)

	data111 := make([]*TGroupUserInfo, 0)
	sqlStr := "select * from t_group_user  where group_id=?"
	err = db.Raw(sqlStr, 319400007).Limit(2).Offset(0).Find(&data111).Error
	if err != nil {
		fmt.Println("获取错误:", err)
	} else {
		fmt.Println("获取当前群组成员成功：", data111)
	}

	var count int64
	err = db.Table("t_group_user").Where("group_id=? and user_id = ? ", 319400007, 3193000006).Count(&count).Error
	fmt.Println(count)

}

func TestChatCustomer(db *gorm.DB) {

	//count1 := 0
	//// raw 的用法
	//sql, err := db.Raw("select count(1) as count from t_group_information").Rows()
	//if err != nil {
	//	fmt.Println("获取数据错误", err)
	//} else {
	//	for sql.Next() {
	//		err = sql.Scan(&count1)
	//		if err != nil {
	//			fmt.Println(err)
	//			return
	//		}
	//		fmt.Println("总数为：", count1)
	//		//for i := range data{
	//		//	fmt.Println("获取数据成功：", data[i])
	//		//}
	//	}
	//
	//}
	//
	//data3 := make([]*TGroupInformation, 0)
	//// raw 的用法
	//err = db.Raw("select * from t_group_information where plat_id=? and create_time>0 ", 190051).Find(&data3).Error
	//if err != nil {
	//	fmt.Println("获取数据错误", err)
	//} else {
	//	fmt.Println("data3:", data3)
	//
	//}

	//data1 := make([]*TGroupInformation, 0)
	//// raw 的用法
	//err = db.Raw("select * from t_group_information where plat_id = 190051").Scan(&data1).Error
	//if err != nil{
	//	fmt.Println("获取数据错误", err)
	//}else {
	//	fmt.Println(data1)
	//
	//}

	// sql21 := "SELECT * FROM `chat_customer`.`t_group_information` WHERE `plat_id` = '190083' "
	//data21 := make([]*TGroupInformation, 0)
	//err = db.Table("t_group_information").
	//	Where("plat_id !=?", 190051).
	//	Where("plat_id not in (190081, 190045)").
	//	//Exec("SELECT * FROM `chat_customer`.`t_group_information` WHERE `plat_id` = '190083' ").
	//	Find(&data21).Error
	//if err!= nil{
	//	fmt.Println(err)
	//}else {
	//	fmt.Println("data21的数据为：")
	//	for i := range data21{
	//		fmt.Println(data21[i])
	//	}
	//}

	//userInfoGroup := make([]*TGroupUserInfo, 0)
	//err := db.Table("t_group_user t1").Select("count(t1.id) user_count, t1.plat_id, t1.group_id, sum(t1.role_id) data_status").
	//	Group("group_id").
	//	Where("plat_id=190051").
	//	Having("user_count > 10").
	//	Having("group_id IN (?)", []int64{111200001}).
	//	Find(&userInfoGroup).Error
	//info := new(TGroupUserInfo)
	//err = db.Select("").Raw("select sum(user_count) user_count, sum(group_id) group_id, plat_id  from ?  t1 GROUP BY plat_id", sqlStr).First(info).Error
	//if err != nil{
	//	fmt.Println("分组错误：", err)
	//}else {
	//	fmt.Println("分组信息为：", userInfoGroup)
	//	for i := range userInfoGroup{
	//		fmt.Println(userInfoGroup[i])
	//	}
	//}

	//	data111 := make([]*TGroupUserInfo, 0)
	//	sqlStr := "select * from t_group_user  where group_id=?"
	//	err = db.Raw(sqlStr, 319400007).Find(&data111).Error
	//	if err != nil{
	//		fmt.Println("获取错误:", err)
	//	}else {
	//		fmt.Println("获取当前群组成员成功：", data111)
	//	}

	//userFriendInfo := new(TUserFriendRemarks)
	//userFriendInfo.FriendRemark = "小丸子2"
	//userFriendInfo.FriendType = 1
	//userFriendInfo.UserId = 3204000002
	//userFriendInfo.PlatId = 190051
	//userFriendInfo.FriendId = 111111111
	//userFriendInfo.CreateTime = time.Now()
	//userFriendInfo.UpdateTime = time.Now()
	/*	userFriendInfo1 := new(TUserFriendRemarks)
		userFriendInfo.FriendRemark = "小丸子3"
		userFriendInfo.FriendType = 1
		userFriendInfo.UserId = 3204000002
		userFriendInfo.PlatId = 190051
		userFriendInfo.FriendId = 2222222222
		userFriendInfo.CreateTime = time.Now()
		userFriendInfo.UpdateTime = time.Now()*/
	/*data := make([]*TUserFriendRemarks, 0)
	data = append(data, userFriendInfo, userFriendInfo1)
	err = db.Table("t_user_friend_remark").Select("plat_id, user_id, friend_type, friend_id, friend_remark").Update(&data).Error
	if err != nil{
		fmt.Println("新增用户备注信息失败：", err)
	}*/

	//userFriendInfo3 := new(TUserFriendRemarks)
	//fmt.Println(userFriendInfo3)
	//if userFriendInfo3 == nil{
	//	fmt.Println("userFriendInfo3 不存在")
	//}else {
	//	fmt.Println("userFriendInfo3 存在")
	//}
	//
	//var count int64
	//err = db.Table("t_user_friend_remark").Where("id=?", 111).Count(&count).Error
	//if err != nil{
	//	fmt.Println("asdfafdsafdddd", err)
	//}
	//fmt.Println("数量为：", count)
	//var has bool
	//fmt.Println(has)
	TestGroupInfoCount(db)
	TestGroupInfos(db)
}

func TestGroupInfos(db *gorm.DB) {
	// 读取
	group := make([]*TGroupInformationHaha, 0)
	err := db.Table("t_group_information").Where("group_id IN (?)", []int{319300002}).Scan(&group).Error
	if err != nil {
		fmt.Println("err:", err)
	} else {
		fmt.Println("群信息为：", group[0])
	}

}

func TetstGroupInfo1(db *gorm.DB) {
	// 读取
	group := make([]*TGroupInformation, 0)
	err := db.Table("t_group_information").Where("group_id IN (?)", []int{319300002}).Find(&group).Error
	if err != nil {
		fmt.Println("err:", err)
	} else {
		fmt.Println(group[0])
	}
	cc := []int{1, 2, 34, 56}
	fmt.Println(cc)

}

func TestGroupInfo2(db *gorm.DB) {
	var count int64
	err := db.Table("t_group_information").Row().Scan(&count)
	if err != nil {
		fmt.Println("err:", err)
	} else {
		fmt.Println(count)
	}

	// 查询数量用这个
	err = db.Table("t_group_information").Select("count(1) count").Count(&count).Error
	if err != nil {
		fmt.Println("err:", err)
	} else {
		fmt.Println(count)
	}

}

func TestGroupInfo3(db *gorm.DB) {
	// 更新一条数据
	var count int64
	err := db.Table("t_group_information").Exec("").Error
	if err != nil {
		fmt.Println("err:", err)
	} else {
		fmt.Println(count)
	}

	str := db.Table("t_group_information").Select("id, group_name, group_id, create_time").Where("plat_id=?", 190051).SubQuery()
	fmt.Println(str)
	//data := make([]*TGroupInformation, 0)
	//err = db.Raw("select * from ? as t1", str).Find(&data).Error
	//if err != nil{
	//	fmt.Println("获取数据错误", err)
	//}else {
	//	for i := range data{
	//		fmt.Println("获取数据成功：", data[i])
	//	}
	//}

}

func TestGroupInfoCount(db *gorm.DB) {
	// 更新一条数据
	var count int64
	err := db.Table("t_group_information force index(idx_plat_live_group) ").Where("plat_id=? ", 190051).Count(&count).Error
	if err != nil {
		fmt.Println(err)
	} else {
		fmt.Println("总数为：", count)
	}

	//data := make([]*TGroupInformation, 0)
	//err = db.Raw("select * from ? as t1", str).Find(&data).Error
	//if err != nil{
	//	fmt.Println("获取数据错误", err)
	//}else {
	//	for i := range data{
	//		fmt.Println("获取数据成功：", data[i])
	//	}
	//}

	data := new(TGroupUserInfo)
	fmt.Println("data:", data.Id)

}
